﻿CREATE TRIGGER [TRG_Data_Update]
ON [dbo].[Data]
FOR UPDATE
AS
BEGIN
	SET NOCOUNT ON

	-- Get all children
	;WITH DataChildrenCte AS
	(
		SELECT DataId, DataName, DataFullPath, 1 as Lvl
		FROM [dbo].[Data]
		WHERE DataId IN (SELECT DataId FROM inserted)

		UNION ALL

		SELECT D.DataId, D.DataName, CASE WHEN M.DataFullPath IS NULL THEN '' ELSE M.DataFullPath + '.' END + M.DataName, M.Lvl + 1 as Lvl
		FROM [dbo].[Data] D  
		INNER JOIN DataChildrenCte M ON M.DataId = D.ParentDataId
	)
	UPDATE	D
	SET		D.[DataFullPath] =	C.DataFullPath
	FROM	[dbo].[Data] D
	INNER JOIN DataChildrenCte C ON C.DataId = D.DataId
	WHERE D.DataId NOT IN (SELECT DataId FROM inserted)
		AND D.[DataFullPath] <> C.DataFullPath

END
